HW#4 --- last modified February 17 2019 19:38:14..

Solution set.

Due date: Apr 27

Files to be submitted:
  Hw4.zip

Purpose: Experiment with management issues such as granting privileges. Be able to use the command facilities to understand what is going on in the database at a given time.

Related Course Outcomes:

The main course outcomes covered by this assignment are:

(4) -- Understand DB2 performance issues and know how to manage them

(5) -- State basic DB2 management issues.

(6) -- Understand the new challenges in today's data management world and DB2's solution to them.

Specification:

Your Hw4.zip file should contain two main files: Assignment.html and Lab.php . The assignment file should contain your solutions to the multiple choice problems below. The lab file should contain transcripts and screenshots from your SPUFI and control center sessions as well as anything else needed to assignment the lab portion of the assignment.

Assignment Component

For this portion you should answer the following multiple choice problems. To do this you might need to examine the online DB2 Reference Center. Besides choosing what you think is the correct answer to each question you should provide a short explanation in your own words justifying your answer. An answer without a coherent justification will not receive any credit.

1. Which of the following is NOT true for SQL?
   1. SQL can be used to query data.
   2. SQL can be used to define data structures in DBMS.
   3. SQL can be used to control access to data.
   4. SQL can be used to select a data access path
2. What database is NOT contained in SYSIBM.SYSDATABASE?
   1. DSNDB06
   2. DSNDB07
   3. DSNDB01
   4. None of the above
3. Which of the following statement has NO "BUFFERPOOL" parameter?
   1. Create database
   2. Create tablespace
   3. Create table
   4. Create index
4. Which type of the following stored procedure needs to go through a two-step pre-compile?
   1. External stored procedure
   2. SQL-external
   3. SQL-native
   4. None of the above
5. How many rows will be returned by the following SQL statement?

   SELECT Col1 FROM TableA GROUP BY Col1;
   

   Here is the content of TableA:

   Col1 	...
   001	...
   002	...
   005	...
   003	...
   004	...
   005	...
   002	...
   NULL	...
   NULL	...
   

   1. 4
   2. 5
   3. 6
   4. 7
6. Terry creates a tableB and wants to set up a foreign key for tableB column B1 as reference to table A columnA1. His intention is that whenever a row in table A is deleted, the corresponding rows in tableB are also deleted by DB2. What is the FOREIGN KEY reference clause this should contain?
   1. ON DELETE RESTRICT
   2. ON DELETE SET NULL
   3. ON DELETE RESTRICT NO ENFORCE
   4. ON DELETE CASCADE
7. Which of the following is a residual predicate?
   1. deptName like 'DB2%'
   2. salary + bonus > :hv_sum
   3. deptName = 'SALE'
   4. salary < :hv_salary
8. Which of the following fetch related claim is false?
   1. One section is shared among the "declare cursor", "open cursor", "fetch", and "close cursor" statements.
   2. A fetch statement can return 0, 1, or multiple rows.
   3. A fetch statement cannot be issued before a cursor is successfully opened.
   4. Sometimes, you do not need to declare the cursor to fetch the result table.
9. Which of the following is NOT true for static SQL statements?
   1. Prepared once, executed multiple times
   2. Prepared before the program is executed
   3. Static SQL is hard-coded
   4. Static SQL uses parameter marks
10. Which of the following is NOT a purpose of using a stored procedure?
    1. Execute multiple remote SQL statements
    2. Allow static SQL from a dynamic environment
    3. Secure business logic in mainframe
    4. Reduce the code path length of application.
11. Which of the following authentication process goes through a SIGNON exit routine?
    1. TSO
    2. BATCH
    3. CICS/IMS
    4. SPUFI
12. Which of the following is true regarding the privileges in packages & plan for static SQL?
    1. The ID binding the plan requires the privileges to execute all SQL statements in the DBRM
    2. The ID executing the plan requires the privileges to execute all SQL statements in the DBRM
    3. The execution privileges of a plan is revoked once a base table in the plan is altered.
    4. The granting of the user privileges to execute package / plan eliminates the need of granting table privileges separately.
    1. i
    2. ii, iii
    3. i, iv
    4. all of the above

Lab Component

Varian has now posted Labs 9, 10. To do the lab component of this homework, go through these labs answering the questions therein. Keep a transcript of your SPUFI sessions. Put these all into Lab.html). Use the same manual method for creating html files as was used for Hw1.

Point Breakdown